﻿using DevExpress.XtraEditors;
using NPOI.HSSF.UserModel;
using NPOI.HSSF.Util;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace Lamex.mDashboard.Common
{
    public class ExcelHelpers
    {
        public static bool DataTableToExcel(DataTable dt1, DataTable dt2, string FilePath)
        {
            bool result = false;
            IWorkbook workbook = null;
            FileStream fs = null;
            IRow row = null;
            ISheet sheet = null;
            ICell cell = null;
            int iRow = 0;
            int iCol = 0;
            try
            {
                // 2007版本  
                if (FilePath.IndexOf(".xlsx") > 0)
                    workbook = new XSSFWorkbook();
                // 2003版本  
                else if (FilePath.IndexOf(".xls") > 0)
                    workbook = new HSSFWorkbook();
                if (dt1 != null && dt1.Rows.Count > 0)
                {
                    //第一个sheet
                    sheet = workbook.CreateSheet("钢柜生产力");//创建一个名称为Sheet0的表 
                    int rowCount = dt1.Rows.Count;//行数
                    int columnCount = dt1.Columns.Count;//列数
                                                        //设置列头  
                    row = sheet.CreateRow(0);//excel第一行设为列头  
                    for (int c = 0; c < columnCount; c++)
                    {
                        cell = row.CreateCell(c);
                        cell.SetCellValue(dt1.Columns[c].ColumnName);
                    }
                    //单元格颜色
                    ICellStyle s = workbook.CreateCellStyle();
                    s.FillForegroundColor = HSSFColor.Yellow.Index;
                    s.FillPattern = FillPattern.SolidForeground;
                    //设置每行每列的单元格,  
                    for (int i = 0; i <= rowCount; i++)
                    {
                        iRow = i;//定义错误行赋值
                        row = sheet.CreateRow(i + 1);
                        for (int j = 0; j < columnCount; j++)
                        {
                            //处理最后一行
                            if (i == rowCount)
                            {
                                double aluminqty = 0, convertqty = 0, workhourqty = 0;
                                XSSFFormulaEvaluator evalor = new XSSFFormulaEvaluator(workbook);
                                //整行赋予颜色
                                for (int m = 0; m < columnCount; m++)
                                {
                                    cell = row.CreateCell(m);
                                    cell.CellStyle = s;
                                    //求和函数                            
                                    if (dt1.Columns[m].ColumnName == "铝材数量")
                                    {
                                        cell.SetCellFormula(string.Format("SUM(B{0}:B{1})", 2, i + 1));
                                        var formulaValue = evalor.Evaluate(cell);
                                        aluminqty = formulaValue.NumberValue;
                                    }
                                    else if (dt1.Columns[m].ColumnName == "钢柜数量")
                                        cell.SetCellFormula(string.Format("SUM(C{0}:C{1})", 2, i + 1));
                                    else if (dt1.Columns[m].ColumnName == "钢柜换算数量")
                                    {
                                        cell.SetCellFormula(string.Format("SUM(D{0}:D{1})", 2, i + 1));
                                        var formulaValue = evalor.Evaluate(cell);
                                        convertqty = formulaValue.NumberValue;
                                    }
                                    else if (dt1.Columns[m].ColumnName == "喷粉工时(h)")
                                    {
                                        cell.SetCellFormula(string.Format("SUM(E{0}:E{1})", 2, i + 1));
                                        var formulaValue = evalor.Evaluate(cell);
                                        workhourqty = formulaValue.NumberValue;
                                    }
                                    else if (dt1.Columns[m].ColumnName == "日期")
                                        cell.SetCellValue("合计：");
                                    else if (dt1.Columns[m].ColumnName == "生产力")
                                    {
                                        if (workhourqty > 0)
                                            cell.SetCellValue(Math.Round((aluminqty + convertqty) / workhourqty, 2));
                                    }

                                }
                                break;
                            }
                            iCol = j;
                            if (dt1.Columns[j].ColumnName == "铝材数量" || dt1.Columns[j].ColumnName == "钢柜数量" ||
                            dt1.Columns[j].ColumnName == "钢柜换算数量" || dt1.Columns[j].ColumnName == "喷粉工时(h)" ||
                            dt1.Columns[j].ColumnName == "生产力")
                            {
                                double values = dt1.Rows[i][j].ToString().Length > 0 ?
                                    Convert.ToDouble(dt1.Rows[i][j].ToString()) : 0;
                                cell = row.CreateCell(j, CellType.Numeric);
                                cell.SetCellValue(values);
                            }
                            else
                            {
                                cell = row.CreateCell(j);
                                cell.SetCellValue(dt1.Rows[i][j].ToString());
                            }

                        }
                    }
                    using (fs = File.OpenWrite(FilePath))
                    {
                        workbook.Write(fs);//向打开的这个xls文件中写入数据  
                        result = true;
                    }
                }
                if (dt2 != null && dt2.Rows.Count > 0)
                {

                    //第二个sheet
                    sheet = workbook.CreateSheet("铝材扫码清单");//创建一个名称为Sheet0的表 
                    int rowCount = dt2.Rows.Count;//行数
                    int columnCount = dt2.Columns.Count;//列数
                                                        //设置列头  
                    row = sheet.CreateRow(0);//excel第一行设为列头  
                    for (int c = 0; c < columnCount; c++)
                    {
                        cell = row.CreateCell(c);
                        cell.SetCellValue(dt2.Columns[c].ColumnName);
                    }

                    //设置每行每列的单元格,  
                    for (int i = 0; i < rowCount; i++)
                    {
                        iRow = i;//定义错误行赋值
                        row = sheet.CreateRow(i + 1);
                        for (int j = 0; j < columnCount; j++)
                        {
                            iCol = j;
                            if (dt2.Columns[j].ColumnName == "部件数量" || dt2.Columns[j].ColumnName == "入仓工时")
                            {
                                double values = dt2.Rows[i][j].ToString().Length > 0 ?
                                    Convert.ToDouble(dt2.Rows[i][j].ToString()) : 0;
                                cell = row.CreateCell(j, CellType.Numeric);
                                cell.SetCellValue(values);
                            }
                            else
                            {
                                cell = row.CreateCell(j);
                                cell.SetCellValue(dt2.Rows[i][j].ToString());
                            }

                        }
                    }
                    using (fs = File.OpenWrite(FilePath))
                    {
                        workbook.Write(fs);//向打开的这个xls文件中写入数据  
                        result = true;
                    }
                }

                return result;
            }
            catch (Exception ex)
            {

                if (fs != null)
                {
                    fs.Close();
                }
                XtraMessageBox.Show("第" + iRow.ToString() + "行" + "第" + iCol.ToString() + "列" + ex.Message);
                return false;

            }
        }

    }
}
